﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for DataAccess
/// </summary>
public class DataAccess
{
    SqlConnection conn;
    SqlDataAdapter Adapter;
    public DataAccess()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public SqlConnection getConnect()
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["OFDConnectionString"].ConnectionString);
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
            conn.Open();
        }
        else
        {
            conn.Open();
        }
        return conn;
    }


    internal DataTable executeSelect(string spName)
    {
        DataSet dataSet = new DataSet();
        //Open connection
        conn = getConnect();

        Adapter = new SqlDataAdapter(spName, conn);
        Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;


        Adapter.Fill(dataSet);
        //Close connection
        CloseConnection(conn);

        return dataSet.Tables[0];
    }
    internal DataTable executeSelect(string spName, SqlParameter[] paraList)
    {
        DataSet dataSet = new DataSet();
        //Open connection
        conn = getConnect();

        Adapter = new SqlDataAdapter(spName, conn);
        Adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        Adapter.SelectCommand.Parameters.AddRange(paraList);

        Adapter.Fill(dataSet);
        //Close connection
        CloseConnection(conn);

        return dataSet.Tables[0];
    }

    internal int executeModifier(string spName, SqlParameter[] paraList)
    {
        int result = 0;
        //Open connection
        conn = getConnect();

        SqlCommand command = new SqlCommand(spName, conn);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddRange(paraList);

        try
        {
            result = command.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            //relationship error
            result = 0;
        }
        //Close connection
        CloseConnection(conn);

        return result;
    }

    public void CloseConnection(SqlConnection sqlCon)
    {
        if (sqlCon != null)
        {
            if (sqlCon.State == ConnectionState.Open)
            {
                sqlCon.Close();
            }
            sqlCon.Dispose();
        }
    }





    
}